﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using WebBanThuoc.DTO;

namespace WebBanThuoc.DAO
{
    public class KhachHang_DAO
    {
         private SqlConnection connection;
        //public bool hasData { set; get; }
        public KhachHang_DAO(SqlConnection connection)
        {
            this.connection = connection;
        }

        public int createIDKhachHang()
        {
            int result = 1;
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, "SELECT [dbo].[fnc_CreateNew_IDKhachHang] () as col");
                if (dap.HasData)
                {
                    dap.DaReader.Read();
                    result = Convert.ToInt32(dap.DaReader["col"]);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        private List<KhachHang> getList_WithPassword_By(string whereCom, string orderCom)
        {
            List<KhachHang> data = new List<KhachHang>();
            string comStr = "Select * from [KhachHang]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        KhachHang obj = new KhachHang();

                        obj.IDKhachHang =  Convert.ToInt32(dap.DaReader["IdKhachHang"]);
                        obj.Email= dap.DaReader["Email"].ToString();
                        obj.HoTen = dap.DaReader["HoTen"].ToString(); 
                        obj.DiaChi = dap.DaReader["DiaChi"].ToString();
                        obj.SoDienThoai = dap.DaReader["SoDienThoai"].ToString();
                        obj.Phai = dap.DaReader["Phai"].ToString();
                        obj.NgaySinh = Convert.ToDateTime(dap.DaReader["NgaySinh"]);
                        obj.MatKhau= dap.DaReader["MatKhau"].ToString();
                        obj.NgayTao= Convert.ToDateTime(dap.DaReader["NgayTao"]);
                        obj.IsStatus = Convert.ToInt32(dap.DaReader["IsStatus"]);
                        
                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public List<KhachHang> getList_WithPassword_ByKey()
        {
            return getList_WithPassword_By("", " [HoTen] asc");
        }

        private List<KhachHang> getList(string whereCom, string orderCom)
        {
            List<KhachHang> data = new List<KhachHang>();
            string comStr = "Select * from [KhachHang]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                //this.hasData = dap.HasData;
                if (dap.HasData)
                {
                    while (dap.DaReader.Read())
                    {
                        KhachHang obj = new KhachHang();

                        obj.IDKhachHang = Convert.ToInt32(dap.DaReader["IdKhachHang"]);
                        obj.Email = dap.DaReader["Email"].ToString();
                        obj.HoTen = dap.DaReader["HoTen"].ToString();
                        obj.DiaChi = dap.DaReader["DiaChi"].ToString();
                        obj.SoDienThoai = dap.DaReader["SoDienThoai"].ToString();
                        obj.Phai = dap.DaReader["Phai"].ToString();
                        obj.NgaySinh = Convert.ToDateTime(dap.DaReader["NgaySinh"]);
                        obj.MatKhau = dap.DaReader["MatKhau"].ToString();
                        obj.NgayTao = Convert.ToDateTime(dap.DaReader["NgayTao"]);
                        obj.IsStatus = Convert.ToInt32(dap.DaReader["IsStatus"]);

                        data.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public List<KhachHang> getListByKey(string HoTen)
        {
            return getList(" [HoTen] like N'%" + HoTen + "%' ", " [IdKhachHang] asc");
        }

        public List<KhachHang> getListByKey()
        {
            return getList("", " [IdKhachHang] asc");
        }

        private KhachHang getBy(string whereCom, string orderCom)
        {
            KhachHang data = null;
            string comStr = "Select * from [KhachHang]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                if (dap.HasData)
                {
                    data = new KhachHang();
                    dap.DaReader.Read();

                    data.IDKhachHang = Convert.ToInt32(dap.DaReader["IdKhachHang"]);
                    data.Email = dap.DaReader["Email"].ToString();
                    data.HoTen = dap.DaReader["HoTen"].ToString();
                    data.DiaChi = dap.DaReader["DiaChi"].ToString();
                    data.SoDienThoai = dap.DaReader["SoDienThoai"].ToString();
                    data.Phai = dap.DaReader["Phai"].ToString();
                    data.NgaySinh = Convert.ToDateTime(dap.DaReader["NgaySinh"]);
                    data.MatKhau = dap.DaReader["MatKhau"].ToString();
                    data.NgayTao = Convert.ToDateTime(dap.DaReader["NgayTao"]);
                    data.IsStatus = Convert.ToInt32(dap.DaReader["IsStatus"]);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public KhachHang loginByKey(string Email, string MatKhau)
        {
            return getBy(" [Email]=N'" + Email + "' and [MatKhau]=N'" + MatKhau + "' ", "");
        }
        public KhachHang getByKey(int IDKhachHang)
        {
            return getBy(" [IDKhachHang]=" + IDKhachHang, "");
        }

        public int add(KhachHang obj)  //thêm khách hàng
        {
           
            string comQuery = "INSERT INTO [KhachHang] ([IDKhachHang],[Email]"
                + ",[HoTen],[DiaChi],[SoDienThoai],[Phai],[NgaySinh],[MatKhau],[IsStatus],[NgayTao])"
                + " VALUES (@IDKhachHang,@Email,@HoTen,@DiaChi,@SoDienThoai"
                + ",@Phai,@NgaySinh,@MatKhau,@IsStatus,@NgayTao)";

            try
            {
                IDataProvider dap = new DataProvider(connection);
                //if (obj.IDKhachHang == null || obj.IDKhachHang.ToString() == "")
              //  {
                //    obj.IDKhachHang = createIDKhachHang();
               // }
                
                dap.AddParameters("@IDKhachHang", obj.IDKhachHang, System.Data.SqlDbType.Int);
                dap.AddParameters("@Email", obj.Email, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@HoTen", obj.HoTen, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@DiaChi", obj.DiaChi, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@SoDienThoai", obj.SoDienThoai, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@Phai", obj.Phai, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@NgaySinh", obj.NgaySinh, System.Data.SqlDbType.SmallDateTime);
                dap.AddParameters("@MatKhau", obj.MatKhau, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@IsStatus",obj.IsStatus, System.Data.SqlDbType.Int);
                dap.AddParameters("@NgayTao", DateTime.Now, System.Data.SqlDbType.SmallDateTime);
               
                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }        

        private int delete(string whereCom)
        {
            string comUpdate = "DELETE FROM [KhachHang] ";
            if (whereCom.Trim() != "")
                comUpdate += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(this.connection);
                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comUpdate);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int deleteByKey(int IDKhachHang)
        {
            return delete(" [IDKhachHang]=" + IDKhachHang);
        }

        private int update(KhachHang obj, string whereCom)
        {
            string comQuery = "UPDATE [KhachHang] SET [IDKhachHang]=@IDKhachHang, [Email]=@Email, [HoTen]=@HoTen"
                + ", [DiaChi]=@DiaChi, [SoDienThoai]=@SoDienThoai, [Phai]=@Phai, [NgaySinh]=@NgaySinh"
                + ", [MatKhau]=@MatKhau, [NgayTao]=@NgayTao, [IsStatus]=@IsStatus ";                
            if (whereCom.Trim() != "")
                comQuery += " where " + whereCom;
            try
            {
                IDataProvider dap = new DataProvider(connection);
                dap.AddParameters("@IDKhachHang", obj.IDKhachHang, System.Data.SqlDbType.Int);
                dap.AddParameters("@Email", obj.Email, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@HoTen", obj.HoTen, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@DiaChi", obj.DiaChi, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@SoDienThoai", obj.SoDienThoai, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@Phai", obj.Phai, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@NgaySinh", obj.NgaySinh, System.Data.SqlDbType.SmallDateTime);
                dap.AddParameters("@MatKhau", obj.MatKhau, System.Data.SqlDbType.NVarChar);
                dap.AddParameters("@IsStatus", obj.IsStatus, System.Data.SqlDbType.Int);
                dap.AddParameters("@NgayTao", DateTime.Now, System.Data.SqlDbType.SmallDateTime);

                return dap.ExecuteNonQuery_Command(System.Data.CommandType.Text, comQuery);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int updateByKey(KhachHang data, int IDKhachHang)
        {
            return update(data, " [IDKhachHang]=" + IDKhachHang);
        }
        public KhachHang checkEmail(string Email)
        {
            return getBy(" [Email]='" + Email + "' ", "");
        }

        private KhachHang getBy(string whereCom, string orderCom, List<SqlParameter> dataPrama)
        {
            KhachHang data = null;
            string comStr = "Select * from [KhachHang]";
            if (whereCom.Trim() != "")
                comStr += " where " + whereCom;
            if (orderCom.Trim() != "")
            {
                comStr += " order by " + orderCom;
            }

            try
            {
                IDataProvider dap = new DataProvider(connection);
                if (dataPrama != null)
                {
                    dap.AddParameters(dataPrama);
                }
                dap.ExecuteReader_Command(System.Data.CommandType.Text, comStr);
                if (dap.HasData)
                {
                    data = new KhachHang();
                    dap.DaReader.Read();

                    data.IDKhachHang = Convert.ToInt32(dap.DaReader["IdKhachHang"]);
                    data.Email = dap.DaReader["Email"].ToString();
                    data.HoTen = dap.DaReader["HoTen"].ToString();
                    data.DiaChi = dap.DaReader["DiaChi"].ToString();
                    data.SoDienThoai = dap.DaReader["SoDienThoai"].ToString();
                    data.Phai = dap.DaReader["Phai"].ToString();
                    data.NgaySinh = Convert.ToDateTime(dap.DaReader["NgaySinh"]);
                    data.MatKhau = dap.DaReader["MatKhau"].ToString();
                    data.NgayTao = Convert.ToDateTime(dap.DaReader["NgayTao"]);
                    data.IsStatus = Convert.ToInt32(dap.DaReader["IsStatus"]);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return data;
        }

        public KhachHang loginByParam(string Email, string MatKhau)
        {
            List<SqlParameter> dataPrama = new List<SqlParameter>();
            dataPrama.Add(
                new SqlParameter("@Email", System.Data.SqlDbType.NVarChar)
                {
                    Value = Email
                });
            dataPrama.Add(
                new SqlParameter("@MatKhau", System.Data.SqlDbType.NVarChar)
                {
                    Value = MatKhau
                });
            return getBy(" [Email]=@Email and [MatKhau]=@MatKhau ", "", dataPrama);
        }
    }
}